﻿/**
 * 
 */
package dao;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

import bean.Cart;
import bean.Order;
import util.JdbcUtils;

import java.sql.Connection;

public class cartDao {
	
	
	/**
	 * 查询用户id在购物车表中的记录数（也就是用户加入购物车的商品有多少个）
	 */
	public static int queryCount(String id) {
		String sql = "SELECT COUNT(*) record FROM shop_cart WHERE id=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				return rs.getInt("record");
			}
			rs.close();
			pstmt.close();
			conn.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
		return 0;
	}
	/**
	 * 查询出用户id在购物车表中的每条记录，并用HashMap数组存储数据
	 * @param id
	 * @return
	 */
	public static Cart[] queryCart(String id) {
		String sql = "SELECT * FROM shop_cart WHERE id=?";
		int record = 0;
		record = queryCount(id);
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Cart[] cart = new Cart[record];
		for(int i = 0;i<record;i++) {
			cart[i] = new Cart();
		}
		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			int index = 0;
			while(rs.next()) {
				cart[index].setBid(rs.getString("bid"));
				cart[index].setBname(rs.getString("bname"));
				cart[index].setAuthor(rs.getString("author"));
				cart[index].setAmount(rs.getInt("amount"));
				cart[index].setSum(rs.getBigDecimal("sum"));
				index++;
			}

			return cart;
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
				rs.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return null;
	}
	
	public static Cart queryProduct(String id,String bid) {
		String sql = "SELECT * FROM shop_cart WHERE id=? and bid=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Cart cart = new Cart();
		cart.setId(id);
		cart.setBid(bid);
		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, bid);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				cart.setBname(rs.getString("bname"));
				cart.setAuthor(rs.getString("author"));
				cart.setAmount(rs.getInt("amount"));
				cart.setSum(rs.getBigDecimal("sum"));
				return cart;
			}
			rs.close();
			pstmt.close();
			conn.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 判断购物车中是否有该商品,可用存储过程优化
	 * @param cart
	 * @return
	 */
	public static int isHaveProduct(Cart cart) {
		String sql = "SELECT COUNT(*) record FROM shop_cart WHERE id=? and bid=?;";
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, cart.getId());
			pstmt.setString(2, cart.getBid());
			rs = pstmt.executeQuery();
			if(rs.next()) {
				return rs.getInt("record");
			}
			rs.close();
			pstmt.close();
			conn.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
		return 0;
	}
	/**
	 * 因为购物车表中已有商品记录，现在是更新商品的数量和总金额，可用存储过程优化
	 * @param cart
	 * @return
	 */
	public static boolean updateProduct(Cart cart) {
		String sql = "UPDATE shop_cart SET amount=amount+1,sum=(sum/(amount-1))*amount WHERE id=? AND bid=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		int row = 0;
		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, cart.getId());
			pstmt.setString(2, cart.getBid());
			row = pstmt.executeUpdate();
			if(row>0) {
				return true;
			}
			pstmt.close();
			conn.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	/**
	 * 向购物车表插入商品记录，已用存储过程优化
	 * @param cart
	 * @return
	 */
	public static boolean insertProduct(Cart cart) {
		
		String sql = "call insert_cart (?,?,?,?,?,?)";		
		Connection conn = null;
		PreparedStatement pstmt = null;
		int row = 0;
		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, cart.getId());
			pstmt.setString(2, cart.getBid());
			pstmt.setString(3, cart.getBname());
			pstmt.setString(4, cart.getAuthor());
			pstmt.setInt(5, cart.getAmount());
			pstmt.setBigDecimal(6, cart.getSum());
			row = pstmt.executeUpdate();
			if(row == 1) {
				return true;
			}
			
			pstmt.close();
			conn.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	/**
	 * 删除购物车表中的商品记录
	 * @param id
	 * @param bid
	 * @return
	 */
	public static boolean deleteProduct(String id,String bid) {
		String sql = "DELETE FROM shop_cart WHERE id=? and bid=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		int row = 0;
		try {
			conn = JdbcUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, bid);
			row = pstmt.executeUpdate();
			if(row>1) {
				return true;
			}
			pstmt.close();
			conn.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
		return false;
		
	}
	
}
